/**
 * 
 */
package cn.remex.db.rsql;


import cn.remex.db.Container;
import cn.remex.db.Database;
import cn.remex.db.DbRvo;
import cn.remex.db.exception.RsqlException;
import cn.remex.db.rsql.connection.RDBSpaceConfig;
import cn.remex.db.rsql.connection.dialect.Dialect;
import cn.remex.db.rsql.model.ModelableImpl;
import cn.remex.db.sql.ColumnType;
import cn.remex.db.sql.FieldType;
import cn.remex.db.sql.SqlType;
import cn.remex.db.utils.Assert;
import cn.remex.db.utils.Judgment;
import cn.remex.db.utils.exception.ServiceCode;
import cn.remex.db.utils.reflect.ReflectUtil;

import javax.persistence.*;
import java.lang.reflect.Type;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author yangyang
 * 
 *  本对象本希望采用池从处理所有的条件处理，但此处并为采用，望以后处理 此处采用的是对象clone方法来解决条件冲突的。不止效率如何。
 * @author Hengyang Liu  yangyang8599@163.com
 * @since 2013-6-23
 *
 */
public final class RsqlDefiner extends Database implements RsqlConstants {
	private static void renameTable(final Dialect dialect, final String oldTableName, final String newTableName) {
		String sql = dialect.renameTableSql();
		Database.getSession().executeUpdate(sql.replaceAll(":oldTableName",dialect.quoteKey(oldTableName)).replaceAll(":newTableName",dialect.quoteKey(newTableName)), null);
	}
	private static void modifyColumn(String tableName, DbRvo columnNames, ColumnType ct, String columnName, Dialect dialect,String oldColumnName){//如果没有修改 必须将oldColumnName = columnName
		List<Object> cns = columnNames.getCells(0, columnName, 0);//存在的列名，要么有一个要么没有
		String newColumnName = columnName;
		boolean notRename = cns.size() == 1 //如果属性名已经有了,则notRename=true
				|| (cns.size()==0 && columnNames.getCells(0, oldColumnName, 0).size()==0);//如果属性名没有且oldname在列中也没有，都算notRename=true LHY 2016-6-28

		if (cns.size() == 0 && notRename) { //没有,也不是从别的列重命名过来的。
			RsqlUtils.alterAddColumn(dialect, tableName, columnName, ct);
			logger.info("现已完成为名为" + tableName + "的表添加列" + columnName);
		}else{ // 有
			oldColumnName = notRename?columnName:oldColumnName;
			String type = columnNames.getCells(0, oldColumnName, "DATA_TYPE").get(0).toString();
			Object length = columnNames.getCells(0, oldColumnName, "DATA_LENGTH").get(0);
			Object precision = columnNames.getCells(0, oldColumnName, "DATA_PRECISION").get(0);
			Object scale = columnNames.getCells(0, oldColumnName, "DATA_SCALE").get(0);
			String nullable = columnNames.getCells(0, oldColumnName, "NULLABLE").get(0).toString();
			String sqlTypeStr = dialect.obtainSQLTypeString(ct).trim();
			
//			if(notRename&&ct.type == Types.CLOB) return;
			boolean needSetNullable = nullable.startsWith("Y")!=ct.nullable;//oracle中 Y N  mysql中 YES NO
			boolean flag = !needSetNullable && notRename;//

			if(flag&&(type.equalsIgnoreCase("INT") && sqlTypeStr.toUpperCase().startsWith("INTEGER"))){
				logger.info("Types.INT 对应为 INTEGER，无需重构类型!");
			}else if(flag&&ct.type == Types.BOOLEAN && type.equalsIgnoreCase("VARCHAR") && String.valueOf(length).equals("5")) {
				logger.info("Types.BOOLEAN 对应为 VARCHAR(5)，无需重构类型!");
			}else if(flag&&ct.type == Types.DOUBLE && type.equalsIgnoreCase("DOUBLE")
					&& String.valueOf(ct.length).equalsIgnoreCase(String.valueOf(precision)) && String.valueOf(2).equalsIgnoreCase(String.valueOf(scale))) { //暂时默认都两位
				logger.info("Types.DOUBLE 对应为 DOUBLE("+precision+","+scale+")，无需重构类型!,默认为2位");
			}else if(flag&&ct.type == Types.NUMERIC && (type.equalsIgnoreCase("NUMERIC") || type.equalsIgnoreCase("DECIMAL"))
					&& String.valueOf(ct.length).equalsIgnoreCase(String.valueOf(precision)) && String.valueOf(ct.scale).equalsIgnoreCase(String.valueOf(scale))) { //暂时默认都两位
				logger.info("Types.NUMERIC 对应为 "+type.toUpperCase()+"("+precision+","+scale+")，无需重构类型!,默认为0位");
			}else if(flag&& (
					(ct.type == Types.DATE && type.equalsIgnoreCase("DATE"))
					||(ct.type == Types.TIME && type.equalsIgnoreCase("TIME"))
					||(ct.type == Types.TIMESTAMP && (type.equalsIgnoreCase("TIMESTAMP") || type.equalsIgnoreCase("DATETIME")))
						)
					) { //暂时默认都两位
				logger.info("Types.DATE/TIME/TIMESTAMP 对应为 "+type.toUpperCase()+"，无需重构类型!,默认为0位");
			}else if(flag&&ct.type == Types.CLOB && (type.equalsIgnoreCase("TEXT") || type.equalsIgnoreCase("CLOB")) ) {
				logger.info("Types.CLOB 对应为 "+type.toUpperCase()+"，无需重构类型!");
			}else if(!flag
					|| !sqlTypeStr.split("[ \t(]")[0].equalsIgnoreCase(type)
					|| !String.valueOf(ct.length).equalsIgnoreCase(String.valueOf(length))
					) { // 长度和类型不同时需修改
				logger.info("修改表 " + tableName + " 列" + newColumnName+" from "+oldColumnName+":"+type+"("+length+") NULLABLE="+nullable+" ->" +dialect.obtainSQLTypeString(ct));
				RsqlUtils.alterModifyColumn(dialect, tableName, newColumnName, ct, oldColumnName);
				logger.info("完成修改表 " + tableName + " 列" + newColumnName+" from "+oldColumnName+":"+type+"("+length+") NULLABLE="+nullable+" ->" +dialect.obtainSQLTypeString(ct));
			}
		}
	
	}
	/* 此函数一检查了base object在内的基本表数据列，是否需要修改类型，长度等*/
	public static void refreshORMBaseTables(final RDBSpaceConfig spaceConfig) {
		Map<String, Class<?>> ormBeans = spaceConfig.getOrmBeans();
		Dialect dialect = spaceConfig.getDialect();
		logger.debug("▄▄▄▄▄▄▄▄▄▄◢RemexDB的ORM组件正在检查数据库中的 基本表BaseTables◣▄▄▄▄▄▄▄▄▄▄");
		// 此语句仅适用于mssql查询数据库中表名
		Container session = Database.getSession();
		DbRvo tableNames = session.executeQuery(dialect.obtainSQLSelectTableNames(), null);

		for (String beanName : ormBeans.keySet()) {
			refreshThisBaseTable(dialect,ormBeans,beanName,tableNames,spaceConfig.getSpaceName());
		}
		logger.debug("▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄END▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄");

	}
	private static void refreshThisBaseTable(Dialect dialect, Map<String, Class<?>> ormBeans, String beanName, DbRvo tableNames, String spaceName) {
		String tableName = dialect.needLowCaseTableName()? beanName.toLowerCase():beanName;
		//检查是否重命名
		cn.remex.db.rsql.transactional.RsqlDefine rsqlDefine = ormBeans.get(beanName).getAnnotation(cn.remex.db.rsql.transactional.RsqlDefine.class);

		if (!Judgment.nullOrBlank(rsqlDefine) && !Judgment.nullOrBlank(rsqlDefine.renameFrom()) && tableNames.getCells(0, rsqlDefine.renameFrom(), 0).size() == 1) {//非空则重命名
			renameTable(dialect, rsqlDefine.renameFrom(), tableName);
			logger.info("修改表名from " + rsqlDefine.renameFrom()+" to " + tableName + "完成！");
			modifyBaseTables(tableName, ormBeans.get(beanName), dialect, spaceName);
		} else if (tableNames.getCells(0, tableName, 0).size() != 1) { //非空则重命名
			RsqlUtils.createBaseTable(dialect, tableName, ormBeans.get(beanName));
			logger.info("创建名为" + tableName + "的表完成！");
		} else {
			modifyBaseTables(tableName, ormBeans.get(beanName), dialect, spaceName);
		}
	}
	private static void modifyBaseTables(String tableName, Class<?> beanClass, Dialect dialect, String spaceName){

		logger.debug("数据库中存在名为" + tableName + "的表！跳过创建阶段，进行表结构检查！");

		// 添加需要检查的列s
		List<Map<String, ColumnType>> columnsList = new ArrayList<>();
		Map<String, ColumnType> sysColumns = RsqlUtils.obtainColumnTypeOfSysColumn();
		columnsList.add(sysColumns);// 检查系统列
		Map<String, ColumnType> baseColumns = RsqlUtils.obtainColumnType(beanClass, null, FieldType.TBase);
		columnsList.add(baseColumns);// 检查基础数据列
		Map<String, ColumnType> objectColumns = RsqlUtils.obtainColumnType(beanClass, null, FieldType.TObject);
		columnsList.add(objectColumns);// 检查一对一数据列
		
		// 获取数据库中对应表的列s
		HashMap<String, Object> params = new HashMap<>();
		params.put("tableName", tableName);
//		params.put(PN_rowCount, "0");
		DbRvo columnNames = Database.getSession(spaceName).executeQuery(dialect.obtainSQLSelectTablesColumnNames(tableName), params);
		
		for (Map<String, ColumnType> columns : columnsList) {
			for (String columnName : columns.keySet()) {
				cn.remex.db.rsql.transactional.RsqlDefine rsqlDefine = ReflectUtil.getAnnotation(beanClass, columnName, cn.remex.db.rsql.transactional.RsqlDefine.class);
				String oldColumnName = rsqlDefine==null || Judgment.nullOrBlank(rsqlDefine.renameFrom())?columnName:rsqlDefine.renameFrom();
				modifyColumn(tableName, columnNames, columns.get(columnName), columnName, dialect,oldColumnName);
			}
		}
	
	}
	/*此函数一检查了collection object在内的外键表CollectionTables*/
	public static void refreshORMCollectionTables(final RDBSpaceConfig spaceConfig) {
		Map<String, Class<?>> ormBeans = spaceConfig.getOrmBeans();
		Dialect dialect = spaceConfig.getDialect();
		logger.debug("▄▄▄▄▄▄▄▄▄▄◢RemexDB的ORM组件正在检查数据库中的外键表CollectionTables◣▄▄▄▄▄▄▄▄▄▄▄▄▄");
		// 此语句仅适用于mssql查询数据库中表名
		Container session = Database.getSession();
		DbRvo tableNames = session.executeQuery(dialect.obtainSQLSelectTableNames(),null);

		for (String beanName : ormBeans.keySet()) {
			Class<?> beanClass = ormBeans.get(beanName);
			Map<String, Type> fields = SqlType.getFields(beanClass, FieldType.TCollection);

			for (String fieldName : fields.keySet()) {
				String tableName = RsqlUtils.obtainListColumnFKTableName(dialect, beanName, fieldName);
				tableName = dialect.needLowCaseTableName()? tableName.toLowerCase():tableName;
				Type field = fields.get(fieldName);
				cn.remex.db.rsql.transactional.RsqlDefine curBeanRsqlDefine = ReflectUtil.getAnnotation(beanClass, cn.remex.db.rsql.transactional.RsqlDefine.class);
				cn.remex.db.rsql.transactional.RsqlDefine curFieldrsqlDefine = ReflectUtil.getAnnotation(beanClass, fieldName, cn.remex.db.rsql.transactional.RsqlDefine.class);



				String oldTableName = (Judgment.nullOrBlank(curBeanRsqlDefine) || Judgment.nullOrBlank(curBeanRsqlDefine.renameFrom()))
						&&(Judgment.nullOrBlank(curFieldrsqlDefine) ||Judgment.nullOrBlank(curFieldrsqlDefine.renameFrom())) ?
						tableName : RsqlUtils.obtainListColumnFKTableName(dialect,
						Judgment.nullOrBlank(curBeanRsqlDefine) || Judgment.nullOrBlank(curBeanRsqlDefine.renameFrom()) ?beanName:curBeanRsqlDefine.renameFrom(),
						Judgment.nullOrBlank(curFieldrsqlDefine) || Judgment.nullOrBlank(curFieldrsqlDefine.renameFrom()) ?fieldName:curFieldrsqlDefine.renameFrom());

				if (!(tableNames.getCells(0, tableName, 0).size() == 1)
						&& !(tableNames.getCells(0, oldTableName, 0).size() == 1) ) {//没有找到表名，用renameFrom也没有找到表名
					Class<?> targetClass = ReflectUtil.getListActualType(field);

					OneToMany otm = ReflectUtil.getAnnotation(beanClass, fieldName, OneToMany.class);
					if (otm != null) {// 此为一对多，没有注明一对多则新建中间表,表示多对多
						Map<String, Type> fbFields = SqlType.getFields(targetClass, FieldType.TObject);
						String mappedField = otm.mappedBy();// 指明了OneToMany关系中，外键由多方保管更新，则需要核对多方是否有这个外键
						if (!fbFields.containsKey(mappedField)) {
							throw new RsqlException(ServiceCode.RSQL_INIT_ERROR, "["+beanClass+"]OneToMany映射错误，在多方[ " + targetClass.toString() + " ]未设置外键[ "
									+ mappedField + " ]维护关系。可能是getter/setter的名称设置有误！");
						} else if (!fbFields.get(mappedField).equals(beanClass)) {
							throw new RsqlException(ServiceCode.RSQL_INIT_ERROR, "["+beanClass+"]OneToMany映射错误，在多方设置的属性[ "+mappedField+" ]类型有误，应该为："+beanClass);
						}
					} else {
						Map<String, Type> fbFields = SqlType.getFields(beanClass, FieldType.TCollection);
						//不配置ManyToMany、或者显式配置ManyToMany时，本类为多对多方的主方，负责维护中间表
						ManyToMany mtm = ReflectUtil.getAnnotation(beanClass, fieldName, ManyToMany.class);
						Class<?> fbBeanClass;
						boolean isweihufang;

						if(null!=mtm){
							String mappedField = mtm.mappedBy();// 必须成对出现,不指定时则只有List属性的一方维护此多对多关系
							fbBeanClass = ReflectUtil.getListActualType(fbFields.get(fieldName));
							Class te = mtm.targetEntity();//只能在主方指定，且类型不能错误
							isweihufang = fbBeanClass.equals(te);//如果有注解则TargetEntity有指定


							ManyToMany fk_mtm = ReflectUtil.getAnnotation(targetClass, mappedField, ManyToMany.class);
							if(fk_mtm !=null){
								Assert.isTrue(fieldName.equals(fk_mtm.mappedBy()), ServiceCode.ERROR, "["+beanClass+"]多对多外键对象的属性的ManyToMany注解的mappedBy"+fk_mtm.mappedBy()+"必须与当前模型的list属性名"+fieldName+"一致");
								Assert.isTrue((void.class.equals(te) && fk_mtm.targetEntity().equals(beanClass))
												|| (void.class.equals(fk_mtm.targetEntity()) && te.equals(fbBeanClass)), ServiceCode.FAIL,
										"["+beanClass+"]在多对多关系中，targetEntity只能由维护方指定，指定的值为外键对象的类型:targetEntity="+te+";beanClass="+beanClass+";fbBeanClass="+fbBeanClass
								);
							}
						}else{
							// TODO 后面多对多需要显示声明
							isweihufang =true;
//							for(Annotation fban: fbBeanClass.getAnnotations()){
//								Class<? extends Annotation> fbanc = fban.annotationType();
//								if(fbanc.isAssignableFrom(ManyToMany.class)){
//									isweihufang = !fieldName.equals(((ManyToMany) fban).mappedBy()); //当前注解为null，且对方任何mappedBy指向本属性则不是维护方
//								}
//							}
						}


						//只有当前属性注解为空且未被外键对象mappedBy引用 或者 注解不为null且指定了targetEntity时本属性为维护方
						if(isweihufang){
							RsqlUtils.createCollectionTable(dialect, beanName, fieldName, field);
							logger.info("创建名为" + tableName + "的表完成！ ");
						}
					}
				} else {
					logger.debug("数据库中存在名为" + tableName +" / " +oldTableName + "的表！跳过创建阶段，进行表结构检查！");

					if(tableNames.getCells(0, tableName, 0).size() == 0  && tableNames.getCells(0, oldTableName, 0).size() == 1 && !tableName.equals(oldTableName)){ //新的没有，就得有，且新旧名字不一样
						renameTable(dialect, oldTableName,tableName);
					}

					HashMap<String, Object> params = new HashMap<>();
					params.put("tableName", tableName);
					DbRvo columnNames = session.executeQuery(dialect.obtainSQLSelectTablesColumnNames(tableName), params);

					List<Map<String, ColumnType>> columnsList = new ArrayList<>();
					Map<String, ColumnType> sysColumns = RsqlUtils.obtainColumnTypeOfSysColumn();
					columnsList.add(sysColumns);// 检查系统列
					Map<String, ColumnType> neededColumns = RsqlUtils.obtainListColumnFKColumnType(beanName, fieldName, field);
					columnsList.add(neededColumns);// 添加这个表的必需列

					for (Map<String, ColumnType> columns : columnsList) {
						for (String columnName : columns.keySet()) {
							//获取renameFrom
							Class<?> fieldClass = columnName.startsWith("P_")?beanClass:ReflectUtil.getListActualType(field);
							cn.remex.db.rsql.transactional.RsqlDefine filedRsqlDefine = ReflectUtil.getAnnotation(fieldClass, cn.remex.db.rsql.transactional.RsqlDefine.class);
							boolean notRename = filedRsqlDefine==null || Judgment.nullOrBlank(filedRsqlDefine.renameFrom());
							String fieldOldColumnName = notRename?columnName:filedRsqlDefine.renameFrom();
							fieldOldColumnName = columnName.startsWith("P_")||columnName.startsWith("F_") ? (notRename?columnName:columnName.split("_")[0]+"_"+fieldOldColumnName) :columnName;
							modifyColumn(tableName, columnNames, columns.get(columnName), columnName, dialect, fieldOldColumnName);
						}
					}
				}
			}
		}
		logger.debug("▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄END▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄");

	}
	/*此函数一检查数据库中的约束*/
	public static void refreshORMConstraints(final RDBSpaceConfig spaceConfig) {
		Map<String, Class<?>> ormBeans = spaceConfig.getOrmBeans();
		Dialect dialect = spaceConfig.getDialect();
		logger.debug("▄▄▄▄▄▄▄▄▄▄◢RemexDB的ORM组件正在检查数据库中的约束◣▄▄▄▄▄▄▄▄▄▄▄▄▄");
		Container session = Database.getSession();
		
//		// 测试用 删除所有索引用户索引
//		for(List<Object> row:indexNames.getRows()){
//			String idx = row.get(0).toString();
//			if(idx.toString().startsWith("SYS_") || idx.toString().startsWith("PK_")){
//				continue;
//			}
//			String tbname = row.get(3).toString();
//			String unique = row.get(5).toString();
//			if("UNIQUE".equals(unique)){
//				session.execute("alter table "+dialect.quoteKey(tbname)
//						+" drop constraint "+dialect.quoteKey(idx), null);
//			}else{
//				session.execute("drop index "+idx.toString(), null);
//			}
//		}
		
		
		for (String beanName : ormBeans.keySet()) {
			DbRvo indexNames = session.executeQuery(dialect.obtainSQLSelectIndexs(beanName),null);
			Class<?> beanClass = ormBeans.get(beanName);

			//检查系统索引
			RsqlUtils.SysColumns.keySet().stream()
					.filter(col -> indexNames.getCells(dialect.obtainSQLIndexNameField(), dialect.obtainIndexName(beanName, col), dialect.obtainSQLIndexNameField()).size() == 0)
					.forEach(col -> session.execute(dialect.obtainIndexSql(beanName, dialect.obtainIndexName(beanName, col),col), null));
			SqlType.getFields(beanClass, FieldType.TObject).keySet().stream()
					.filter(col -> indexNames.getCells(dialect.obtainSQLIndexNameField(), dialect.obtainIndexName(beanName, col), dialect.obtainSQLIndexNameField()).size() == 0 && !RsqlUtils.SysColumns.containsKey(col))
					.forEach(col -> session.execute(dialect.obtainIndexSql(beanName, dialect.obtainIndexName(beanName, col),col), null));


			Class curClass = beanClass;
			HashMap<String, String> handled = new HashMap<>();
			while(!ModelableImpl.class.equals(curClass)){
				Table table_anno = ReflectUtil.getAnnotation(curClass, Table.class);
	            if(null != table_anno){
	                //检查自定配置索引
	                Index[] idxs = table_anno.indexes();
	                for(Index idx:idxs){
	                    if(Judgment.nullOrBlank(idx.columnList()))continue;
						String[] listCols = idx.columnList().split(";");
		                String name = Judgment.nullOrBlank(idx.name())?dialect.obtainIndexName(beanName, listCols):idx.name();//约束名称可以自定义，也可以由系统根据方言自动生成。
		                if(handled.containsKey("index_"+name)) continue;//如果子类已经处理则跳过
		                if(listCols.length > 0 && indexNames.getCells(dialect.obtainSQLIndexNameField(), name, dialect.obtainSQLIndexNameField()).size() == 0){
			                logger.debug("为" + beanName +"创建索引：name="+name+";cols="+listCols);
			                session.execute(dialect.obtainIndexSql(beanName, name,listCols), null);
			                logger.debug("完成为" + beanName +"创建索引：name="+name+";cols="+listCols);
		                }
	                }
	                //检查唯一性索引
	                UniqueConstraint[] ucs = table_anno.uniqueConstraints();
	                for(UniqueConstraint uc:ucs){
	                    String[] cols = uc.columnNames();
	                    String name = Judgment.nullOrBlank(uc.name())?dialect.obtainIndexName(beanName, cols):uc.name();//约束名称可以自定义，也可以由系统根据方言自动生成。
		                if(handled.containsKey("unique_"+name)) continue;//如果子类已经处理则跳过
		                if(cols.length > 0 && indexNames.getCells(dialect.obtainSQLIndexNameField(), name, dialect.obtainSQLIndexNameField()).size() == 0){
			                logger.debug("为" + beanName +"创建主键：name="+name+";cols="+cols);
			                session.execute(dialect.obtainConstraintSql(beanName, name,cols), null);
			                logger.debug("完成为" + beanName +"创建主键：name="+name+";cols="+cols);
		                }
	                }
	            }

				curClass = curClass.getSuperclass();
			}



			//检查OneToOne、OneToManay、ManyToMany中的级联
			
			
			
		}
		
	}
}
